Imports Microsoft.VisualBasic
Imports System.Collections.Generic
Imports system.Data.SqlClient

Namespace MyDAO
    Public MustInherit Class SqlStringBuilder

        Public Cmd As SqlCommand

        Public Sub New()
            Me.Cmd = New SqlCommand
            Me.Cmd.CommandType = Data.CommandType.Text
        End Sub

        Public Sub New(ByVal tableName As String)
            Me.New()

            Me.Table = New MyDataTable(tableName)
        End Sub

        Public Sub New(ByVal tableName As String, ByVal aliasName As String)
            Me.New()
            Me.Table = New MyDataTable(tableName, aliasName)
        End Sub

        Public Table As MyDataTableBase

        Public Condition As MyDataConditionBase

        Public Sub AddCondition(ByVal columnName As String, ByVal value As Object)
            Me.AddCondition(columnName, MyEqualityOperator.Equal, value)
        End Sub

        Public Sub AddCondition(ByVal columnName As String, ByVal op As MyEqualityOperator, ByVal value As Object)
            Me.AddCondition(MyLogicalOperator.MyAnd, CreateCondition(New MyColumnExpression(columnName), op, New MyValueExpression(value)))

            'Me.AddWithValue(columnName, value)
            'Me.Conditions.Add(New MyDataCondition(New MyColumnExpression(columnName), New MyValueExpression(value)))
        End Sub

        Public Sub AddVarCondition(ByVal columnName As String, ByVal variable As String)
            Me.AddVarCondition(columnName, MyEqualityOperator.Equal, variable)
        End Sub

        Public Sub AddVarCondition(ByVal columnName As String, ByVal op As MyEqualityOperator, ByVal variable As String)
            Me.AddCondition(MyLogicalOperator.MyAnd, CreateCondition(New MyColumnExpression(columnName), op, New MyVariableExpression(variable)))
        End Sub

        Public Sub AddContainsCondition(Of T As Structure)(ByVal columnName As String, ByVal op As MyContainsOperator, ByVal valueList As List(Of T))
            Dim sourceList As New List(Of MyValueExpression)

            For Each value As T In valueList
                sourceList.Add(New MyValueExpression(value))
            Next

            Me.AddCondition(MyLogicalOperator.MyAnd, CreateCondition(New MyColumnExpression(columnName), op, sourceList))
        End Sub

        Public Sub AddCondition(ByVal op As MyLogicalOperator, ByVal condition As MyDataConditionBase)
            If Me.Condition Is Nothing Then
                Me.Condition = condition
            Else
                Dim condition1 As MyDataConditionBase = Me.Condition
                Me.Condition = New MyCompositeCondition(condition1, op, condition)
            End If

        End Sub

        Public Sub AddCondition(ByVal builder As SqlStringBuilder)
            If builder.Condition IsNot Nothing Then
                Me.AddCondition(MyLogicalOperator.MyAnd, builder.Condition)
            End If
            Me.AddWithValue(builder)
        End Sub

        Public Sub OrCondition(ByVal columnName As String, ByVal value As Object)
            Me.OrCondition(columnName, MyEqualityOperator.Equal, value)
        End Sub

        Public Sub OrCondition(ByVal columnName As String, ByVal op As MyEqualityOperator, ByVal value As Object)
            Me.AddCondition(MyLogicalOperator.MyOr, CreateCondition(New MyColumnExpression(columnName), op, New MyValueExpression(value)))
        End Sub

        Public Sub OrVarCondition(ByVal columnName As String, ByVal variable As String)
            Me.OrVarCondition(columnName, MyEqualityOperator.Equal, variable)
        End Sub

        Public Sub OrVarCondition(ByVal columnName As String, ByVal op As MyEqualityOperator, ByVal variable As String)
            Me.AddCondition(MyLogicalOperator.MyOr, CreateCondition(New MyColumnExpression(columnName), op, New MyVariableExpression(variable)))
        End Sub

        Public Shared Function CreateCondition(ByVal exp1 As MyDataExpressionBase, ByVal op As MyEqualityOperator, ByVal exp2 As MyDataExpressionBase)
            Return New MyEqualityCondition(exp1, op, exp2)
        End Function

        Public Shared Function CreateCondition(ByVal exp1 As MyDataExpressionBase, ByVal op As MyContainsOperator, ByVal sourceList As List(Of MyValueExpression))
            Return New MyContainsCondition(exp1, op, sourceList)
        End Function

        Public Sub AddWithValue(ByVal paremeterName As String, ByVal value As Object)
            Me.Cmd.Parameters.AddWithValue(paremeterName, value)
        End Sub

        Public Sub AddWithValue(ByVal builder As SqlStringBuilder)
            For Each parameter As SqlParameter In builder.Cmd.Parameters
                Me.AddWithValue(parameter.ParameterName, parameter.Value)
            Next
        End Sub

        Public Overridable Function ToStringBuilder() As MyStringBuilder
            Dim sql As MyStringBuilder = Me.Build()

            For Each parameter As SqlParameter In Me.Cmd.Parameters
                sql.AddWithValue(parameter.ParameterName, parameter.Value)
            Next

            Return sql
        End Function

        Public MustOverride Function Build() As MyStringBuilder

        Public Overrides Function ToString() As String
            Return Me.Build.ToString()
        End Function

        Public Relations As New List(Of MyDataRelationBase)


        Public Sub LeftJoin(ByVal tableName As String, ByVal leftKey As String, ByVal rightKey As String)
            Me.Relations.Add(New MyLeftJoinRelation(New MyDataTable(tableName), leftKey, rightKey))
        End Sub

        Public Sub LeftJoin(ByVal tableName As String, ByVal aliasName As String, ByVal leftKey As String, ByVal rightKey As String)
            Me.Relations.Add(New MyLeftJoinRelation(New MyDataTable(tableName, aliasName), leftKey, rightKey))
        End Sub

        Public Sub InnerJoin(ByVal tableName As String, ByVal leftKey As String, ByVal rightKey As String)
            Me.Relations.Add(New MyInnerJoinRelation(New MyDataTable(tableName), leftKey, rightKey))
        End Sub

        Public Sub InnerJoin(ByVal tableName As String, ByVal aliasName As String, ByVal leftKey As String, ByVal rightKey As String)
            Me.Relations.Add(New MyInnerJoinRelation(New MyDataTable(tableName, aliasName), leftKey, rightKey))
        End Sub

        Public Sub LeftJoin(ByVal table As SelectStringBuilder, ByVal aliasName As String, ByVal leftKey As String, ByVal rightKey As String)
            Me.Relations.Add(New MyLeftJoinRelation(New MySubqueryTable(table, aliasName), leftKey, rightKey))
        End Sub

        Public Sub InnerJoin(ByVal table As SelectStringBuilder, ByVal aliasName As String, ByVal leftKey As String, ByVal rightKey As String)
            Me.Relations.Add(New MyInnerJoinRelation(New MySubqueryTable(table, aliasName), leftKey, rightKey))
        End Sub

    End Class

End Namespace

